#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2022/5/18 11:21
# @Author  : Grayson Liu
# @Email   : graysonliu@foxmail.com
# @File    : dw.py.py

import pandas as pd
import os
import sqlalchemy
import sqlite3


class DwUtil:
    """
    统一数仓层的工具类
    """
    ods_dw_name_mapping = {
        "Company": "company",
        "System": "system_name",
        "Result": "result",
        "# cores": "num_core",
        "Processor": "processor",
        "CPU Speed(MHz)": "cpu_speed",
        "1st Cache per core(KB)": "first_cache",
        "2nd Cache per core(KB)": "second_cache",
        "3rd Cache per chip(MB)": "third_cache",
        "Other Cache per chip(MB)": "other_cache",
        "Memory(GB)": "memory",
        "Updated": "updated",
        "Report Link": "report_link"
    }
    name_list = ["cpu2006_fprate", "cpu2006_fpspeed", "cpu2006_intrate", "cpu2006_intspeed",
                 "cpu2017_fprate", "cpu2017_fpspeed", "cpu2017_intrate", "cpu2017_intspeed",
                 "jbb2015_comp", "jbb2015_dist", "jbb2015_multi",
                 "jvm2008", "power_ssj2008"]

    # def __init__(self, ods_folder_path: str = r"../data/ods",   # jupeter
    #              dw_folder_path: str = r"../data/dw",
    #              db_path: str = r"../db/user.db"):
    def __init__(self, ods_folder_path: str = "data/ods",
                 dw_folder_path: str = "data/dw",
                 db_path: str = "db/user.db"):
        """
        构造方法，传入相应参数
        :param ods_folder_path: 贴源数据层文件目录路径
        :param dw_folder_path: 数据仓库层文件目录路径
        :param db_path: 数据库文件路径
        """
        self.ods_folder_path = ods_folder_path
        self.dw_folder_path = dw_folder_path
        self.db_path = db_path
        self.engine = sqlalchemy.create_engine(f"sqlite:///{db_path}")

    def make_dw_file(self):
        """
        将贴源数据层的数据文件进行处理，将列名进行统一，生成统一数仓层的文件，以便后续直接导入数据库
        :return:
        """
        for name in DwUtil.name_list:
            data = pd.read_csv(os.path.join(self.ods_folder_path, f"ods_{name}.csv"), index_col=0)
            data = data.rename(columns=DwUtil.ods_dw_name_mapping)
            data.index.name = "id"
            data.index = data.index + 1
            data.to_csv(os.path.join(self.dw_folder_path, f"dw_{name}.csv"))

    def import_to_db(self):
        """
        将统一数仓层的文件导入SQLite数据库
        :return:
        """
        conn = sqlite3.connect(self.db_path)
        for name in DwUtil.name_list:
            c = conn.cursor()
            with open("script/create_dw_template", "r", True, 'UTF-8') as f:
                sql = f.read().format(name, name, name, name, name)
                c.executescript(sql)
            data = pd.read_csv(os.path.join(self.dw_folder_path, f"dw_{name}.csv"), index_col=0)
            data.to_sql(f"dw_{name}", conn, if_exists="append")
            conn.commit()
            c.close()
        conn.close()

    def execute_sql(self, sql: str) -> pd.DataFrame:
        """
        执行查询操作，返回DataFrame
        :param sql: 要执行的SQL语句
        :return: 以DataFrame形式的查询结果
        """
        conn = sqlite3.connect(self.db_path)
        query_data = pd.read_sql_query(sql, conn)
        conn.close()
        return query_data
